import datetime
from bank_manage_system.database.db_connector import DatabaseConnector


class CustomerManager:
    def __init__(self, db: DatabaseConnector):
        self.db = db

    def add_customer(self, data: dict) -> dict:
        """添加新客户"""
        # 检查身份证号唯一性
        pid = data.get('PID')
        existing = self.db.execute_query(
            "SELECT PID FROM userinfo WHERE PID = %s",
            (pid,)
        )
        if existing:
            return {"success": False, "message": "该身份证号已存在"}

        # 构建完整数据
        full_data = {
            'customerName': data.get('customerName', ''),
            'PID': pid,
            'telephone': data.get('telephone', ''),
            'address': data.get('address', ''),

            'createdAt': datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        }

        # 插入数据库
        query = """
        INSERT INTO userinfo 
        (customerName, PID, telephone, address)
        VALUES (%(customerName)s, %(PID)s, %(telephone)s, %(address)s)
        """
        try:
            self.db.execute_query(query, full_data, commit=True)
            return {"success": True, "message": "客户添加成功"}
        except Exception as e:
            return {"success": False, "message": f"添加失败: {str(e)}"}

    def update_customer(self, customer_id: int, update_data: dict) -> dict:
        """更新客户信息 """
        # 过滤允许更新的字段
        allowed_fields = {'customerName', 'telephone', 'address'}
        filtered_data = {k: v for k, v in update_data.items() if k in allowed_fields}

        if not filtered_data:
            return {"success": False, "message": "无有效更新数据"}

        # 构建UPDATE语句
        set_clause = ", ".join([f"{k} = %({k})s" for k in filtered_data])
        query = f"UPDATE userinfo SET {set_clause} WHERE customerID = %(customerID)s"

        # 添加主键参数
        filtered_data['customerID'] = customer_id

        try:
            self.db.execute_query(query, filtered_data, commit=True)
            return {"success": True, "message": "更新成功"}
        except Exception as e:
            return {"success": False, "message": f"更新失败: {str(e)}"}

    def delete_customer(self, customer_id: int) -> dict:
        """删除客户 """
        # 检查是否有银行卡关联
        cards = self.db.execute_query(
            "SELECT COUNT(*) AS cnt FROM cardinfo WHERE customerID = %s",
            (customer_id,)
        )
        if cards and cards[0]['cnt'] > 0:
            return {"success": False, "message": "客户关联银行卡，不能删除"}

        try:
            self.db.execute_query(
                "DELETE FROM userinfo WHERE customerID = %s",
                (customer_id,),
                commit=True
            )
            return {"success": True, "message": "删除成功"}
        except Exception as e:
            return {"success": False, "message": f"删除失败: {str(e)}"}

    def search_customers(self, query_type: str, value: str) -> dict:
        """客户信息查询 """
        if query_type == "exact":
            # 精确查询使用customerID和PID字段
            query = "SELECT * FROM userinfo WHERE customerID = %s OR PID = %s"
            params = (value, value)
        elif query_type == "fuzzy":
            # 模糊查询使用customerName、telephone和address字段
            value = f"%{value}%"
            query = "SELECT * FROM userinfo WHERE customerName LIKE %s OR telephone LIKE %s OR address LIKE %s"
            params = (value, value, value)
        elif query_type == "range":
            try:
                # 范围查询只支持customerID
                start_id, end_id = map(int, value.split(","))
                query = "SELECT * FROM userinfo WHERE customerID BETWEEN %s AND %s"
                params = (start_id, end_id)
            except:
                return {"success": False, "message": "无效的范围格式"}
        else:
            return {"success": False, "message": "无效的查询类型"}

        try:
            results = self.db.execute_query(query, params)
            return {"success": True, "data": results}
        except Exception as e:
            return {"success": False, "message": f"查询失败: {str(e)}"}

